package com.my.db.dataInsert;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.sql.DataSource;
import java.sql.*;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeParseException;
import java.time.temporal.ChronoUnit;
import java.util.*;
import java.util.concurrent.*;
import java.util.concurrent.atomic.AtomicInteger;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

/**
 * 农事记录批量插入工具类
 * 功能：根据地块、品种关联关系，按农事类型批量插入park_farming表数据
 * 特性：分批次处理、批量插入、事务管理、多线程处理、连接池管理、数据缓存、品种特定时间配置
 */
public class FarmingRecordBatchInsert {
    private static final Logger logger = LoggerFactory.getLogger(FarmingRecordBatchInsert.class);

    // 数据库连接参数
    private static final String DB_URL = "jdbc:mysql://12/yilong?useUnicode=true&characterEncoding=utf8" +
            "&autoReconnect=true" +
            "&zeroDateTimeBehavior=CONVERT_TO_NULL" +
            "&transformedBitIsBoolean=true" +
            "&allowPublicKeyRetrieval=true" +
            "&serverTimezone=Asia/Shanghai";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "112";

    // 处理参数配置
    private static final int BATCH_SIZE = 500;         // 每批次插入数量
    private static final int THREAD_POOL_SIZE = 4;     // 线程池大小
    private static DataSource dataSource;              // 数据库连接池

    // 业务基础参数
    private static final String PARK_ID = "2222";      // 园区ID
    private static final int STATE = 1;                // 状态固定为1
    private static final String DEFAULT_CREATE_BY = "system";
    private static final String DEFAULT_SYS_ORG_CODE = "A02";
    private static final DateTimeFormatter DATE_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
    // 可配置的偏移范围参数
    private static final int MIN_OFFSET_DAYS = -2;  // 最小偏移天数（负数表示向前偏移）
    private static final int MAX_OFFSET_DAYS = 2;   // 最大偏移天数（正数表示向后偏移）
    // 截止日期常量定义
    private static final int DEADLINE_MONTH = 8;
    private static final int DEADLINE_DAY = 31;

    // 植保类型（3）的执行人列表
    private static final List<String[]> PLANT_PROTECTION_EXECUTORS = Arrays.asList(
            new String[]{"李明", "13985695786"},    // 原执行人
            new String[]{"张建国", "13856874524"},  // 新增执行人1
            new String[]{"王丽娟", "13965432109"},  // 新增执行人2
            new String[]{"赵志强", "13786543210"},   // 新增执行人3
            new String[]{"吴桂芳", "13881484575"},
            new String[]{"刘小强", "13551883865"},
            new String[]{"刘贵明", "13436522589"},
            new String[]{"王先坤", "13678562584"}
    );
    private static final Random RANDOM = new Random(); // 用于随机分配执行人

    // 农事类型-执行人-电话映射
    private static final Map<Integer, String[]> TYPE_EXECUTOR_MAP = new HashMap<Integer, String[]>() {{
        put(0, new String[]{"吴桂芳", "13881484575"});  // 育苗
        put(1, new String[]{"刘小强", "13551883865"});  // 耕地地
        put(36, new String[]{"李明", "13985695786"}); // 底肥施用管理（将被转换为3）
        put(30, new String[]{"吴桂芳", "13881484575"}); // 种子预处理（将被转换为3）
        put(35, new String[]{"李明", "13985695786"}); // 生长期施肥管理（将被转换为3）
        put(2, new String[]{"刘小强", "13551883865"});  // 种植
        put(3, new String[]{"", ""});                   // 植保（使用随机分配的执行人列表）
        put(5, new String[]{"李明", "13985695786"});    // 采收
        put(4, new String[]{"刘贵明", "13436522589"});  // 烘干
        put(9, new String[]{"刘贵明", "13436522589"});  // 仓储
        put(6, new String[]{"王先坤", "13678562584"});  // 运输
        put(7, new String[]{"王先坤", "13678562584"});  // 加工
    }};

    // 品种-农事类型-时间规则映射（key:variety_id，value:Map<type, [startTime, endTime, remarks, imgPath]>）
    private static Map<String, Map<Integer, String[]>> VARIETY_TYPE_TIME_MAP;

    // 数据缓存
    private static List<String> datianIds;                     // 地块ID列表
    private static List<String> varietyIds;                    // 品种ID列表
    private static Map<String, String> datianVarietyMap;       // 地块-品种映射（key:datian_id, value:variety_id）

    // SQL语句
    private static final String SELECT_DATIAN_IDS = "SELECT id FROM park_datian WHERE park_id = ? AND del_flag = 0 and id!='1957789389552095234'";
    private static final String SELECT_VARIETY_IDS = "SELECT id FROM park_variety WHERE park_id = ? AND del_flag = 0";
    private static final String SELECT_DATIAN_VARIETY_MAP = "SELECT datian_id, variety_id FROM park_plant WHERE park_id = ? AND del_flag = 0";
    private static final String INSERT_FARMING_SQL = "INSERT INTO park_farming (" +
            "id, park_id, datian_id, variety_id, type, state, executor, phone, " +
            "start_time, end_time, remarks, guidance, img_path, del_flag, " +
            "create_by, create_time, update_by, update_time, sys_org_code" +
            ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, " +
            "CASE WHEN ? = '' THEN NULL ELSE ? END, ?, ?, ?, ?, ?, ?)";  // 图片路径条件插入

    // 植保备注内容数组
    private static final String[] PLANT_PROTECTION_REMARKS = {
            "种子用咪鲜胺浸种或拌种，杀灭种子携带的病菌",
            "深耕翻土减少越冬病虫害，采用丙草胺处理土壤抑制杂草萌发",
            "秧苗期人工拔除或喷施氰氟草酯等除草剂",
            "发病初期喷施甲霜灵或恶霉灵防治病害",
            "用噻虫嗪防治蓟马和蚜虫",
            "用氯虫苯甲酰胺防治螟虫，吡蚜酮防治稻飞虱",
            "叶瘟用三环唑，纹枯病用井冈霉素",
            "结合耘田人工除草，或喷施五氟磺草胺等除草剂",
            "穗颈瘟在破口前 3 - 5 天喷施三环唑，稻曲病用戊唑醇预防",
            "重点防治螟虫和稻飞虱，选用甲维盐 + 烯啶虫胺组合",
            "对于大龄杂草人工拔除，避免争夺养分",
            "监测稻飞虱密度，达防治指标时用噻嗪酮控制",
            "谷粒瘟可补喷稻瘟灵",
            "及时清除田间剩余杂草，防止其结籽落入田间"
    };

    // 施肥备注内容数组
    private static final String[] FERTILIZER_REMARKS = {
            "施用有机肥2000-3000公斤/亩，改善土壤结构",
            "底肥以农家肥为主，配合适量复合肥(氮磷钾比例15:15:15)",
            "深耕时施用磷酸二铵15-20公斤/亩作为底肥",
            "结合耕地施入腐熟有机肥，提升土壤肥力",
            "底肥选用缓释肥，提高肥料利用率"
    };

    // 静态初始化
    static {
        // 初始化连接池
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(DB_URL);
        config.setUsername(DB_USER);
        config.setPassword(DB_PASSWORD);
        config.setMaximumPoolSize(THREAD_POOL_SIZE);
        config.setConnectionTimeout(30000);
        config.setIdleTimeout(600000);
        config.setMaxLifetime(1800000);
        config.setMinimumIdle(2);
        dataSource = new HikariDataSource(config);

        try {
            // 加载基础数据
            datianIds = loadDatianIds();
            varietyIds = loadVarietyIds();
            datianVarietyMap = loadDatianVarietyMap();

            // 初始化品种-农事类型时间映射
            initVarietyTypeTimeMap();

            logger.info("基础数据缓存完成：地块{}个，品种{}个，地块-品种关联{}条",
                    datianIds.size(), varietyIds.size(), datianVarietyMap.size());
        } catch (SQLException e) {
            logger.error("基础数据加载失败", e);
            throw new RuntimeException("初始化数据缓存失败", e);
        }
    }


    /**
     * 初始化品种-农事类型时间规则映射
     * 水稻保留育苗前的拌种，其他作物种植后的植保将在数据生成时随机生成2-3次
     * 数组格式：[startTime, endTime, remarks, imgPath]，imgPath为空则不插入
     */
    private static void initVarietyTypeTimeMap() {
        VARIETY_TYPE_TIME_MAP = new HashMap<>();
        //基础
        String gengdi = "temp/耕地_1756193765604.png";//        耕地   temp/农机耕地_1757172083349.jpg
        String difei = "temp/底肥_1756193783575.png";//        底肥

        String shuidao_yumiao = "temp/育苗_1756193748277.png";//        水稻育苗
        String shuidao_zhongzhi = "temp/种植_1756193797650.png";//        水稻种植  temp/农机插秧水稻_1757172119661.jpg
        String shuidao_zhibao = "temp/植保1_1756193813551.png";//        水稻植保1
        String shuidao_caishou = "temp/123_1755599109582.png";//        水稻采收 temp/农机收割_1757172159135.jpg
        String shuidao_yunshu = "temp/运输_1756193843418.png";//        水稻运输 temp/运输_1757172211789.jpg
        //玉米
        String yumi_zhongzhi = "temp/玉米种植_1756194771771.png";//        玉米种植
        String yumi_zhibao = "temp/玉米植保_1756194808206.png";//        玉米植保
        String yumi_caishou = "temp/玉米采收_1756194845309.png";//        玉米采收
        //大豆
        String dadou_zhongzhi = "temp/大豆种植_1756194970504.png";
        String dadou_zhibao = "temp/大豆植保_1756195009682.png";
        String dadou_caishou = "temp/大豆采收_1756195035450.png";

        // 1. 南麦941（冬小麦） (1956621899385163778)
        Map<Integer, String[]> nanmai941Times = new HashMap<>();
        nanmai941Times.put(0, new String[]{"2025-02-10 08:00:00", "2025-02-20 18:00:00", "育苗（返青期管理）", "/images/farming/wheat/seedling.jpg"});
        nanmai941Times.put(1, new String[]{"2025-02-25 08:00:00", "2025-03-05 18:00:00", "耕地（春季松土）", gengdi});
        nanmai941Times.put(36, new String[]{"2025-03-06 08:00:00", "2025-03-09 18:00:00", FERTILIZER_REMARKS[1], difei});
        nanmai941Times.put(2, new String[]{"2025-03-10 08:00:00", "2025-03-15 18:00:00", "种植（补苗）", "/images/farming/wheat/planting.jpg"});
        nanmai941Times.put(5, new String[]{"2025-06-10 08:00:00", "2025-06-20 18:00:00", "采收（正季收获）", "/images/farming/wheat/harvest.jpg"});
        nanmai941Times.put(4, new String[]{"2025-06-21 08:00:00", "2025-06-30 18:00:00", "烘干", "/images/farming/wheat/drying.jpg"});
        nanmai941Times.put(9, new String[]{"2025-07-01 08:00:00", "2025-07-05 18:00:00", "仓储", ""});
        nanmai941Times.put(6, new String[]{"2025-07-10 08:00:00", "2025-07-15 18:00:00", "运输", "/images/farming/transport.jpg"});
        nanmai941Times.put(7, new String[]{"2025-07-20 08:00:00", "2025-07-25 18:00:00", "加工", "/images/farming/processing.jpg"});
        VARIETY_TYPE_TIME_MAP.put("1956621899385163778", nanmai941Times);

        // 2. 川麦29（冬小麦） (1957275836178190338)
        Map<Integer, String[]> chuanmai29Times = new HashMap<>();
        chuanmai29Times.put(0, new String[]{"2025-02-15 08:00:00", "2025-02-25 18:00:00", "育苗", "/images/farming/wheat/seedling_chuanmai.jpg"});
        chuanmai29Times.put(1, new String[]{"2025-03-01 08:00:00", "2025-03-08 18:00:00", "耕地地", gengdi});
        chuanmai29Times.put(36, new String[]{"2025-03-09 08:00:00", "2025-03-11 18:00:00", FERTILIZER_REMARKS[1], "/images/farming/wheat/fertilizer36.jpg"});
        chuanmai29Times.put(2, new String[]{"2025-03-12 08:00:00", "2025-03-18 18:00:00", "种植", "/images/farming/wheat/planting.jpg"});
        chuanmai29Times.put(5, new String[]{"2025-06-15 08:00:00", "2025-06-25 18:00:00", "采收", "/images/farming/wheat/harvest.jpg"});
        chuanmai29Times.put(4, new String[]{"2025-06-26 08:00:00", "2025-07-05 18:00:00", "烘干", "/images/farming/wheat/drying.jpg"});
        chuanmai29Times.put(9, new String[]{"2025-07-06 08:00:00", "2025-07-10 18:00:00", "仓储", ""});
        chuanmai29Times.put(6, new String[]{"2025-07-12 08:00:00", "2025-07-18 18:00:00", "运输", "/images/farming/transport.jpg"});
        chuanmai29Times.put(7, new String[]{"2025-07-20 08:00:00", "2025-07-30 18:00:00", "加工", "/images/farming/processing.jpg"});
        VARIETY_TYPE_TIME_MAP.put("1957275836178190338", chuanmai29Times);

        // 3. 隆晶优蒂占（水稻） (1957755503427325954)
        Map<Integer, String[]> longjingyouTimes = new HashMap<>();
        longjingyouTimes.put(30, new String[]{"2025-04-15 08:00:00", "2025-04-17 18:00:00", PLANT_PROTECTION_REMARKS[0], ""});
        longjingyouTimes.put(0, new String[]{"2025-04-18 08:00:00", "2025-05-20 18:00:00", "育苗", shuidao_yumiao});
        longjingyouTimes.put(1, new String[]{"2025-05-21 08:00:00", "2025-05-31 18:00:00", "耕地", gengdi});
        longjingyouTimes.put(36, new String[]{"2025-06-01 08:00:00", "2025-06-03 18:00:00", FERTILIZER_REMARKS[2], difei});
        longjingyouTimes.put(2, new String[]{"2025-06-04 08:00:00", "2025-06-10 18:00:00", "种植", shuidao_zhongzhi});
        longjingyouTimes.put(35, new String[]{"2025-07-10 08:00:00", "2025-07-20 18:00:00", PLANT_PROTECTION_REMARKS[2], shuidao_zhibao});
        longjingyouTimes.put(5, new String[]{"2025-09-01 08:00:00", "2025-09-15 18:00:00", "采收", shuidao_caishou});
        longjingyouTimes.put(4, new String[]{"2025-09-16 08:00:00", "2025-09-20 18:00:00", "烘干", ""});
        longjingyouTimes.put(9, new String[]{"2025-09-21 08:00:00", "2025-09-25 18:00:00", "仓储", ""});
        longjingyouTimes.put(6, new String[]{"2025-09-26 08:00:00", "2025-09-30 18:00:00", "运输", shuidao_yunshu});
        longjingyouTimes.put(7, new String[]{"2025-10-01 08:00:00", "2025-10-05 18:00:00", "加工", ""});
        VARIETY_TYPE_TIME_MAP.put("1957755503427325954", longjingyouTimes);

        // 4. 玉针香（水稻） (1957755597664948226)
        Map<Integer, String[]> yuzhenxiangTimes = new HashMap<>();
        yuzhenxiangTimes.put(30, new String[]{"2025-04-22 08:00:00", "2025-04-24 18:00:00", PLANT_PROTECTION_REMARKS[0], ""});
        yuzhenxiangTimes.put(0, new String[]{"2025-04-25 08:00:00", "2025-06-10 18:00:00", "育苗", shuidao_yumiao});
        yuzhenxiangTimes.put(1, new String[]{"2025-06-01 08:00:00", "2025-06-10 18:00:00", "耕地", gengdi});
        yuzhenxiangTimes.put(36, new String[]{"2025-06-11 08:00:00", "2025-06-13 18:00:00", FERTILIZER_REMARKS[2], difei});
        yuzhenxiangTimes.put(2, new String[]{"2025-06-14 08:00:00", "2025-06-20 18:00:00", "种植", shuidao_zhongzhi});
        yuzhenxiangTimes.put(35, new String[]{"2025-07-10 08:00:00", "2025-07-20 18:00:00", PLANT_PROTECTION_REMARKS[2], shuidao_zhibao});
        yuzhenxiangTimes.put(5, new String[]{"2025-09-01 08:00:00", "2025-09-15 18:00:00", "采收", shuidao_caishou});
        yuzhenxiangTimes.put(4, new String[]{"2025-09-16 08:00:00", "2025-09-20 18:00:00", "烘干", ""});
        yuzhenxiangTimes.put(9, new String[]{"2025-09-21 08:00:00", "2025-09-25 18:00:00", "仓储", ""});
        yuzhenxiangTimes.put(6, new String[]{"2025-09-26 08:00:00", "2025-09-30 18:00:00", "运输", shuidao_yunshu});
        yuzhenxiangTimes.put(7, new String[]{"2025-10-01 08:00:00", "2025-10-05 18:00:00", "加工", ""});
        VARIETY_TYPE_TIME_MAP.put("1957755597664948226", yuzhenxiangTimes);

        // 5. 旱优73（旱稻） (1957755682935148545)
        Map<Integer, String[]> hanyou73Times = new HashMap<>();
        hanyou73Times.put(30, new String[]{"2025-06-10 08:00:00", "2025-06-12 18:00:00", PLANT_PROTECTION_REMARKS[0], ""});
        hanyou73Times.put(1, new String[]{"2025-06-13 08:00:00", "2025-06-20 18:00:00", "耕地", gengdi});
        hanyou73Times.put(36, new String[]{"2025-06-21 08:00:00", "2025-06-23 18:00:00", FERTILIZER_REMARKS[2],difei});
        hanyou73Times.put(2, new String[]{"2025-06-24 08:00:00", "2025-06-30 18:00:00", "种植", shuidao_zhongzhi});
        hanyou73Times.put(35, new String[]{"2025-07-10 08:00:00", "2025-07-20 18:00:00", PLANT_PROTECTION_REMARKS[2], shuidao_zhibao});
        hanyou73Times.put(5, new String[]{"2025-09-20 08:00:00", "2025-09-30 18:00:00", "采收", shuidao_caishou});
        hanyou73Times.put(4, new String[]{"2025-10-01 08:00:00", "2025-10-05 18:00:00", "烘干", ""});
        hanyou73Times.put(9, new String[]{"2025-10-06 08:00:00", "2025-10-10 18:00:00", "仓储", ""});
        hanyou73Times.put(6, new String[]{"2025-10-11 08:00:00", "2025-10-15 18:00:00", "运输", shuidao_yunshu});
        hanyou73Times.put(7, new String[]{"2025-10-16 08:00:00", "2025-10-20 18:00:00", "加工", ""});
        VARIETY_TYPE_TIME_MAP.put("1957755682935148545", hanyou73Times);

        // 6. 成单922（玉米） (1957755765130924033)
        Map<Integer, String[]> chengdan922Times = new HashMap<>();
        chengdan922Times.put(1, new String[]{"2025-06-13 08:00:00", "2025-06-20 18:00:00", "耕地", gengdi});
        chengdan922Times.put(36, new String[]{"2025-06-21 08:00:00", "2025-06-23 18:00:00", FERTILIZER_REMARKS[3], difei});
        chengdan922Times.put(2, new String[]{"2025-06-24 08:00:00", "2025-06-30 18:00:00", "种植", yumi_zhongzhi});
        chengdan922Times.put(3, new String[]{"2025-07-10 08:00:00", "2025-07-15 18:00:00", PLANT_PROTECTION_REMARKS[2], yumi_zhibao});
        chengdan922Times.put(5, new String[]{"2025-09-01 08:00:00", "2025-09-10 18:00:00", "采收", yumi_caishou});
        chengdan922Times.put(4, new String[]{"2025-09-11 08:00:00", "2025-09-15 18:00:00", "烘干",  ""});
        chengdan922Times.put(9, new String[]{"2025-09-16 08:00:00", "2025-09-20 18:00:00", "仓储", ""});
        chengdan922Times.put(6, new String[]{"2025-09-21 08:00:00", "2025-09-25 18:00:00", "运输", ""});
        chengdan922Times.put(7, new String[]{"2025-09-26 08:00:00", "2025-09-30 18:00:00", "加工",  ""});
        VARIETY_TYPE_TIME_MAP.put("1957755765130924033", chengdan922Times);

        // 7. 南农59（大豆） (1957755824237056001)
        Map<Integer, String[]> nannong59Times = new HashMap<>();
        nannong59Times.put(1, new String[]{"2025-06-21 08:00:00", "2025-06-30 18:00:00", "耕地", gengdi});
        nannong59Times.put(36, new String[]{"2025-07-01 08:00:00", "2025-07-03 18:00:00", FERTILIZER_REMARKS[3], difei});
        nannong59Times.put(2, new String[]{"2025-07-04 08:00:00", "2025-07-10 18:00:00", "种植", dadou_zhongzhi});
        nannong59Times.put(3, new String[]{"2025-07-10 08:00:00", "2025-07-15 18:00:00", PLANT_PROTECTION_REMARKS[2], dadou_zhibao});
        nannong59Times.put(5, new String[]{"2025-09-20 08:00:00", "2025-10-10 18:00:00", "采收", dadou_caishou});
        nannong59Times.put(4, new String[]{"2025-10-11 08:00:00", "2025-10-15 18:00:00", "烘干", ""});
        nannong59Times.put(9, new String[]{"2025-10-16 08:00:00", "2025-10-20 18:00:00", "仓储", ""});
        nannong59Times.put(6, new String[]{"2025-10-21 08:00:00", "2025-10-25 18:00:00", "运输", ""});
        nannong59Times.put(7, new String[]{"2025-10-26 08:00:00", "2025-10-30 18:00:00", "加工", ""});
        VARIETY_TYPE_TIME_MAP.put("1957755824237056001", nannong59Times);
    }


    /**
     * 加载地块ID列表
     */
    private static List<String> loadDatianIds() throws SQLException {
        List<String> result = new ArrayList<>();
        try (Connection conn = dataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(SELECT_DATIAN_IDS)) {
            stmt.setString(1, PARK_ID);
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    result.add(rs.getString("id"));
                }
            }
        }
        return result;
    }

    /**
     * 加载品种ID列表
     */
    private static List<String> loadVarietyIds() throws SQLException {
        List<String> result = new ArrayList<>();
        try (Connection conn = dataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(SELECT_VARIETY_IDS)) {
            stmt.setString(1, PARK_ID);
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    result.add(rs.getString("id"));
                }
            }
        }
        return result;
    }

    /**
     * 加载地块-品种映射关系
     */
    private static Map<String, String> loadDatianVarietyMap() throws SQLException {
        Map<String, String> result = new HashMap<>();
        try (Connection conn = dataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement(SELECT_DATIAN_VARIETY_MAP)) {
            stmt.setString(1, PARK_ID);
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    result.put(rs.getString("datian_id"), rs.getString("variety_id"));
                }
            }
        }
        return result;
    }

    /**
     * 生成UUID
     */
    private static String generateId() {
        return UUID.randomUUID().toString().replaceAll("-", "");
    }

    /**
     * 批量插入农事记录主方法
     */
    public static void batchInsertFarmingRecords() {
        logger.info("开始批量插入农事记录...");
        long startTime = System.currentTimeMillis();

        // 创建线程池
        ExecutorService executor = new ThreadPoolExecutor(
                THREAD_POOL_SIZE,
                THREAD_POOL_SIZE,
                0L, TimeUnit.MILLISECONDS,
                new LinkedBlockingQueue<>(),
                new ThreadPoolExecutor.CallerRunsPolicy()
        );

        // 计数器
        AtomicInteger totalInserted = new AtomicInteger(0);
        AtomicInteger failedCount = new AtomicInteger(0);

        // 按地块分组处理
        List<List<String>> datianGroups = splitList(datianIds, THREAD_POOL_SIZE);

        // 为每个地块组提交任务
        for (List<String> datianGroup : datianGroups) {
            executor.submit(() -> {
                try {
                    int inserted = processDatianGroup(datianGroup);
                    totalInserted.addAndGet(inserted);
                } catch (Exception e) {
                    logger.error("处理地块组失败", e);
                    failedCount.incrementAndGet();
                }
            });
        }

        // 关闭线程池并等待所有任务完成
        executor.shutdown();
        try {
            if (!executor.awaitTermination(1, TimeUnit.HOURS)) {
                executor.shutdownNow();
            }
        } catch (InterruptedException e) {
            executor.shutdownNow();
        }

        long endTime = System.currentTimeMillis();
        logger.info("批量插入完成！总耗时: {}ms", (endTime - startTime));
        logger.info("成功插入: {}条记录，失败: {}组", totalInserted.get(), failedCount.get());
    }

    /**
     * 处理地块组，生成并插入农事记录
     */
    private static int processDatianGroup(List<String> datianGroup) throws SQLException {
        int count = 0;
        Connection conn = null;
        PreparedStatement stmt = null;

        try {
            conn = dataSource.getConnection();
            conn.setAutoCommit(false);
            stmt = conn.prepareStatement(INSERT_FARMING_SQL);

            for (String datianId : datianGroup) {
                String varietyId = datianVarietyMap.get(datianId);
                if (varietyId == null || !VARIETY_TYPE_TIME_MAP.containsKey(varietyId)) {
                    logger.warn("地块{}没有关联的有效品种信息，跳过处理", datianId);
                    continue;
                }

                // 获取该品种的农事类型时间映射
                Map<Integer, String[]> typeTimeMap = VARIETY_TYPE_TIME_MAP.get(varietyId);

                // 处理每种农事类型
                for (Map.Entry<Integer, String[]> entry : typeTimeMap.entrySet()) {
                    Integer type = entry.getKey();
                    String[] timeInfo = entry.getValue();

                    // 生成农事记录并添加到批处理
                    count += addFarmingRecordToBatch(stmt, datianId, varietyId, type, timeInfo);

                    // 达到批处理大小则执行
                    if (count % BATCH_SIZE == 0 && count > 0) {
                        stmt.executeBatch();
                        conn.commit();
                        stmt.clearBatch();
                        logger.info("已处理{}条记录（地块: {}）", count, datianId);
                    }
                }

                // 为特定作物添加随机植保记录
                if (isNeedPlantProtection(varietyId)) {
                    count += addRandomPlantProtectionRecords(stmt, datianId, varietyId, typeTimeMap);
                }
            }

            // 处理剩余的记录
            if (count % BATCH_SIZE != 0) {
                stmt.executeBatch();
                conn.commit();
            }

        } catch (SQLException e) {
            if (conn != null) {
                try {
                    conn.rollback();
                } catch (SQLException ex) {
                    logger.error("回滚事务失败", ex);
                }
            }
            logger.error("处理地块组时发生错误", e);
            throw e;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    logger.error("关闭PreparedStatement失败", e);
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    logger.error("关闭Connection失败", e);
                }
            }
        }

        return count;
    }

    /**
     * 判断是否需要添加随机植保记录
     */
    private static boolean isNeedPlantProtection(String varietyId) {
        // 水稻和玉米需要添加随机植保记录
        return "1957755503427325954".equals(varietyId) ||  // 隆晶优蒂占（水稻）
                "1957755597664948226".equals(varietyId) ||  // 玉针香（水稻）
                "1957755682935148545".equals(varietyId) ;  // 旱优73（旱稻）
//                "1957755765130924033".equals(varietyId);     // 成单922（玉米）
    }

    /**
     * 添加随机植保记录
     */
    private static int addRandomPlantProtectionRecords(PreparedStatement stmt, String datianId,
                                                       String varietyId, Map<Integer, String[]> typeTimeMap)
            throws SQLException {
        int count = 0;
        Random random = new Random();

        // 随机生成2-3次植保记录
        int protectionCount = 2 + random.nextInt(2);

        // 获取种植时间作为基准时间
        String[] plantingTime = typeTimeMap.get(2);
        if (plantingTime == null || plantingTime.length < 2) {
            logger.warn("品种{}没有种植时间信息，无法生成植保记录", varietyId);
            return 0;
        }

        try {
            LocalDateTime plantEndTime = LocalDateTime.parse(plantingTime[1], DATE_FORMATTER);
            LocalDateTime harvestStartTime = null;

            // 获取采收时间作为结束时间限制
            if (typeTimeMap.containsKey(5)) {
                String[] harvestTime = typeTimeMap.get(5);
                harvestStartTime = LocalDateTime.parse(harvestTime[0], DATE_FORMATTER);
            } else {
                // 如果没有采收时间，默认使用种植后90天
                harvestStartTime = plantEndTime.plusDays(90);
            }

            // 生成每次植保记录
            for (int i = 0; i < protectionCount; i++) {
                // 计算随机天数（种植后7天到采收前7天之间）
                long minDays = 7;
                long maxDays = ChronoUnit.DAYS.between(plantEndTime, harvestStartTime) - 7;

                if (maxDays <= minDays) {
                    maxDays = minDays + 10; // 确保有足够的时间范围
                }

                long randomDays = minDays + random.nextInt((int)(maxDays - minDays + 1));
                LocalDateTime start = plantEndTime.plusDays(randomDays);
                LocalDateTime end = start.plusHours(4 + random.nextInt(8)); // 持续4-12小时

                // 添加随机偏移
                start = addRandomOffset(start);
                end = addRandomOffset(end);
                // 确保结束时间在开始时间之后
                if (end.isBefore(start)) {
                    end = start.plusHours(1);
                }
                // 检查是否超过截止日期，如果超过则跳过
                if (isAfterDeadline(start) || isAfterDeadline(end)) {
                    logger.info("植保记录日期 {} 至 {} 超过截止日期，不插入数据（地块: {}）",
                            start, end, datianId);
                    continue;
                }

                // 随机选择备注
//                String remark = PLANT_PROTECTION_REMARKS[random.nextInt(PLANT_PROTECTION_REMARKS.length)];
                // 生成2到数组长度-1之间的随机数（包含2和数组长度-1）
//                int index = random.nextInt(PLANT_PROTECTION_REMARKS.length - 2) + 2;
                String remark = PLANT_PROTECTION_REMARKS[random.nextInt(PLANT_PROTECTION_REMARKS.length - 3) + 3];

                // 随机生成图片路径（50%概率有图片）
//                String imgPath = random.nextBoolean() ?
//                        "/images/farming/protection_" + (i+1) + ".jpg" : "";
                String shuidao_zhibao2 = "temp/植保2_1756193822455.png";//水稻植保2，用于随机数据

                // 添加到批处理，类型为3（植保）
                addFarmingRecordToStmt(stmt, datianId, varietyId, 3,
                        start.format(DATE_FORMATTER),
                        end.format(DATE_FORMATTER),
                        remark,
                        shuidao_zhibao2);

                count++;
                // 达到批处理大小则执行
                if (count % BATCH_SIZE == 0) {
                    stmt.executeBatch();
                    stmt.getConnection().commit();
                    stmt.clearBatch();
                }
            }
        } catch (DateTimeParseException e) {
            logger.error("解析日期时间失败", e);
        }

        return count;
    }

    /**
     * 为日期添加随机偏移，确保结果在早上10点到下午6点之间
     */
    private static LocalDateTime addRandomOffset(LocalDateTime dateTime) {
        Random random = new Random();

        // 添加随机天数偏移
        int offsetDays = MIN_OFFSET_DAYS + random.nextInt(MAX_OFFSET_DAYS - MIN_OFFSET_DAYS + 1);
        LocalDateTime result = dateTime.plusDays(offsetDays);

        // 随机生成10-18点之间的小时数（包括10点，不包括18点）
        int hour = 10 + random.nextInt(8); // 8小时范围: 10,11,12,13,14,15,16,17

        // 设置时间为指定小时，分钟和秒数清零
        return result.withHour(hour).withMinute(0).withSecond(0).withNano(0);
    }

    // 判断日期是否超过截止日期的辅助方法
    private static boolean isAfterDeadline(LocalDateTime dateTime) {
        // 检查月份是否大于截止月份，或者月份等于截止月份但日期大于截止日
        return dateTime.getMonthValue() > DEADLINE_MONTH ||
                (dateTime.getMonthValue() == DEADLINE_MONTH && dateTime.getDayOfMonth() > DEADLINE_DAY);
    }


    /**
     * 将农事记录添加到批处理
     * 关键修改：将30、35、36类型转换为3（植保类型），并处理图片路径
     */
    private static int addFarmingRecordToBatch(PreparedStatement stmt, String datianId,
                                               String varietyId, Integer type, String[] timeInfo)
            throws SQLException {
        try {
            String startTimeStr = timeInfo[0];
            String endTimeStr = timeInfo[1];
            String remark = timeInfo.length > 2 ? timeInfo[2] : getDefaultRemark(type);
            // 获取图片路径（第4个元素，默认空字符串）
            String imgPath = timeInfo.length > 3 ? timeInfo[3] : "";

            // 解析并添加随机偏移
            LocalDateTime startTime = LocalDateTime.parse(startTimeStr, DATE_FORMATTER);
            LocalDateTime endTime = LocalDateTime.parse(endTimeStr, DATE_FORMATTER);

            startTime = addRandomOffset(startTime);
            endTime = addRandomOffset(endTime);

            // 检查是否超过截止日期，如果超过则不插入
            if (isAfterDeadline(startTime) || isAfterDeadline(endTime)) {
                logger.info("日期 {} 至 {} 超过截止日期，不插入数据（地块: {}, 类型: {}）",
                        startTime, endTime, datianId, type);
                return 0;
            }

            // 确保结束时间在开始时间之后
            if (endTime.isBefore(startTime)) {
                endTime = startTime.plusHours(1);
            }

            // 关键修改：将30、35、36类型转换为3（植保类型）
            int actualType = type;
            if (type == 30 || type == 35 || type == 36) {
                actualType = 3;
            }

            // 添加到批处理
            addFarmingRecordToStmt(stmt, datianId, varietyId, actualType,
                    startTime.format(DATE_FORMATTER),
                    endTime.format(DATE_FORMATTER),
                    remark,
                    imgPath);  // 传递图片路径

            return 1;
        } catch (DateTimeParseException e) {
            logger.error("解析日期时间失败: {} - {}", timeInfo[0], timeInfo[1], e);
            return 0;
        }
    }

    /**
     * 将农事记录添加到PreparedStatement
     * 关键修改：处理图片路径参数
     */
    private static void addFarmingRecordToStmt(PreparedStatement stmt, String datianId,
                                               String varietyId, Integer type,
                                               String startTime, String endTime,
                                               String remark, String imgPath)
            throws SQLException {
        // 获取执行人和电话
        String[] executorInfo;

        // 对植保类型(3)使用随机分配的执行人
        int randomIndex = RANDOM.nextInt(PLANT_PROTECTION_EXECUTORS.size());
        executorInfo = PLANT_PROTECTION_EXECUTORS.get(randomIndex);

        String executor = executorInfo != null && executorInfo.length > 0 ? executorInfo[0] : DEFAULT_CREATE_BY;
        String phone = executorInfo != null && executorInfo.length > 1 ? executorInfo[1] : "";

        // 设置参数
        int paramIndex = 1;
        stmt.setString(paramIndex++, generateId());          // id
        stmt.setString(paramIndex++, PARK_ID);               // park_id
        stmt.setString(paramIndex++, datianId);              // datian_id
        stmt.setString(paramIndex++, varietyId);             // variety_id
        stmt.setInt(paramIndex++, type);                     // type（已转换为3的类型将使用3）
        stmt.setInt(paramIndex++, STATE);                    // state
        stmt.setString(paramIndex++, executor);              // executor
        stmt.setString(paramIndex++, phone);                 // phone
        stmt.setString(paramIndex++, startTime);             // start_time
        stmt.setString(paramIndex++, endTime);               // end_time
        stmt.setString(paramIndex++, remark);                // remarks
        stmt.setString(paramIndex++, "");                    // guidance
        // 处理图片路径：第一个?用于CASE判断，第二个?用于实际插入值
        stmt.setString(paramIndex++, imgPath);               // 用于CASE WHEN判断
        stmt.setString(paramIndex++, imgPath);               // 实际插入的图片路径
        stmt.setInt(paramIndex++, 0);                        // del_flag
        stmt.setString(paramIndex++, DEFAULT_CREATE_BY);     // create_by
        stmt.setString(paramIndex++, LocalDateTime.now().format(DATE_FORMATTER)); // create_time
        stmt.setString(paramIndex++, DEFAULT_CREATE_BY);     // update_by
        stmt.setString(paramIndex++, LocalDateTime.now().format(DATE_FORMATTER)); // update_time
        stmt.setString(paramIndex++, DEFAULT_SYS_ORG_CODE);  // sys_org_code

        stmt.addBatch();
    }

    /**
     * 获取默认备注
     */
    private static String getDefaultRemark(int type) {
        switch (type) {
            case 0: return "常规育苗管理，确保苗情良好";
            case 1: return "常规耕地作业，疏松土壤";
            case 2: return "按标准种植流程进行种植";
            case 3: return PLANT_PROTECTION_REMARKS[new Random().nextInt(PLANT_PROTECTION_REMARKS.length - 3) + 3];
            case 4: return "标准化烘干处理，控制水分含量";
            case 5: return "成熟作物采收，确保颗粒归仓";
            case 6: return "安全运输，确保作物质量";
            case 7: return "按标准流程加工处理";
            case 9: return "规范化仓储管理，确保存储安全";
            case 30: return "种子预处理，提高发芽率";
            case 35: return FERTILIZER_REMARKS[new Random().nextInt(FERTILIZER_REMARKS.length)];
            case 36: return FERTILIZER_REMARKS[new Random().nextInt(FERTILIZER_REMARKS.length)];
            default: return "常规农事操作";
        }
    }

    /**
     * 将列表拆分为指定大小的子列表
     */
    private static <T> List<List<T>> splitList(List<T> list, int chunkSize) {
        List<List<T>> chunks = new ArrayList<>();
        for (int i = 0; i < list.size(); i += chunkSize) {
            int end = Math.min(i + chunkSize, list.size());
            chunks.add(list.subList(i, end));
        }
        return chunks;
    }

    /**
     * 主方法，程序入口
     */
    public static void main(String[] args) {
        try {
            batchInsertFarmingRecords();
        } catch (Exception e) {
            logger.error("批量插入农事记录失败", e);
            System.exit(1);
        }
        System.exit(0);
    }
}
